<?php
require_once("class.db.php");
require_once("class.meeting.php");

class MeetingSearch {
	
	// Central Office
	const DEFAULT_ADDRESS = "127 Peachtree Street";
	const DEFAULT_CITY = "Atlanta";
	const DEFAULT_ZIP = "30303";
	const DEFAULT_LAT = 33.756887; 
	const DEFAULT_LNG = -84.3880708;
	const DEFAULT_RADIUS = 5;

	private $sql;
	private $where;
	private $args;
	private $argTypes;
	private $columnCount;
	
	private static $locations = array(
		'TRIANGLE CLUB' => array(
			'address' => '702 Lakeshore Circle', 
			'city' => 'Atlanta',
			'zip' => '30324',
			'displayName' => 'Triangle Club'),
		'8111 CLUB' => array(
			'address' => '8111 Roswell Road', 
			'city' => 'Sandy Springs', 
			'zip' => '30350',
			'displayName' => '8111 Club'),
		'N.A.B.A. CLUB' => array(
			'address' => '1809 Briarwood Road', 
			'city' => 'Atlanta',
			'zip' => '30329',
			'displayName' => 'N.A.B.A. Club'),
		'H.O.W. PLACE' => array(
			'address' => '6268 Bells Ferry Road', 
			'city' => 'Woodstock', 
			'zip' => '30102',
			'displayName' => 'H.O.W. Place'),
		'GWINNETT ROOM' => array(
			'address' => '191 Plainview Drive',
			'city' => 'Lawrenceville',
			'zip' =>'30045',
			'displayName' => 'Gwinnett Room'),	
		'GALANO CLUB' => array(
			'address' => '585 Dutch Valley Road', 
			'city' => 'Atlanta', 
			'zip' => '30324',
			'displayName' => 'Galano Club'),	
		'REBOS CLUB' => array(
			'address' => '304 Poplar Street', 
			'city' => 'Marietta', 
			'zip' => '30060',
			'displayName' => 'Rebos Club'),
		'N.O.W. CLUB' => array(
			'address' => '7439 West Strickland Street', 
			'city' => 'Douglasville', 
			'zip' => '30134',
			'displayName' => 'N.O.W. Club'),
		'SERENITY HOUSE' => array(
			'address' => '10102 Old Atlanta Highway', 
			'city' => 'Covington', 
			'zip' => '30014',
			'displayName' => 'Serenity House'),
		'ATLANTA BISCAYNE ROOM' => array(
			'address' => '3659 Clairmont Road', 
			'city' => 'Chamblee', 
			'zip' => '30341',
			'displayName' => 'Atlanta Biscayne Room'),
		'TARA CLUB' => array(
			'address' => '575 N. Central Avenue', 
			'city' =>'Hapeville', 
			'zip' => '30354',
			'displayName' => 'Tara Club'),
		'FREEDOM CLUB' => array(
			'address' => '47 North Fairground Street', 
			'city' => 'Marietta', 
			'zip' => '30060',
			'displayName' => 'Freedom Club'));
	
	public static function getLocations() { 
		return MeetingSearch::$locations;
	}
	
	private function buildSqlNoDistance (SearchParams $params) {
		$this->sql =
			"select m.row, m.name, m.location, m.address1, m.address2, m.city, m.zip, m.locationNotes, "
			."m.quadrant, m.day, d.sequence, m.time, t.sequence, m.codes, m.groupNotes, g.lat, g.lng from "
			."meetings m left outer join days d on m.day = d.day left outer join times t on m.time = t.time "
			."left outer join geocode g on  m.address1 = g.address1 and m.city = g.city and m.zip = g.zip";
			
		
		$this->args = array();
		$this->argTypes = "";
		$this->columnCount = 17;
		$this->where = "";

		$this->addLocationWhere($params);
		
		$this->addGroupWhere($params);
		$this->addDayTimeWhere($params);
		$this->addCodesWhere($params);
				
		if ($this->where) {
			$this->sql .= " where " . $this->where;
		}
		
		$orderby = " order by d.sequence, t.sequence, m.city, m.address1, m.name";
		$this->sql .= " " . $orderby;
	}
	
	private function buildSqlWithDistance (SearchParams $params) {
		$this->sql = 
			"select m.row, m.name, m.location, m.address1, m.address2, m.city, m.zip, m.locationNotes, "
			."m.quadrant, m.day, d.sequence, m.time, t.sequence, m.codes, m.groupNotes, g.lat, g.lng, "
			."round(haversine( ?, ?, g.lat, g.lng),1) distance from meetings m left outer join days d on "
			." m.day = d.day left outer join times t on m.time = t.time left outer join geocode g on "
			." m.address1 = g.address1 and m.city = g.city and m.zip = g.zip"; 
		
		$this->args = array($params->lat, $params->lng);
		$this->argTypes = "dd";
		$this->columnCount = 18;	
		$this->where = "";

		if ($params->searchType == "exact") {
			$this->addLocationWhere($params);		
		} 
		
		$this->addGroupWhere($params);
		$this->addDayTimeWhere($params);
		$this->addCodesWhere($params);
		
		if ($this->where) { 
			$this->sql .= " where " . $this->where;
		}
		
		if ($params->searchType == "nearby") {
				
			$this->sql .= " having distance < ?";
			$radius = ($params->radius) ? $params->radius : self::DEFAULT_RADIUS;
			array_push($this->args, $radius);
			$this->argTypes .= "d";
		}

		$orderby = " order by distance, m.city, m.address1, d.sequence, t.sequence, m.name";
		$this->sql .= " " . $orderby;
	}
	
	private function addCodesWhere(SearchParams $params) {
		
		if ($params->codes) {
			$codesWhere = "";
			foreach ($params->codes as $code) {
				if ($code != "%") {
					if ($codesWhere) {
						$codesWhere .= " or ";
					}					
					
					$codesWhere .= "find_in_set( ?, m.codes) > 0";
					array_push($this->args, strtoupper($code));
					$this->argTypes .= "s";
				}
			}
			
			if ($codesWhere) {
				if ($this->where) {
					$this->where .= " and ";
				}
				$this->where .= " ( " . $codesWhere . " ) ";
			}
		}
	}
	
	private function addGroupWhere(SearchParams $params) {

		if ($params->group) {
			$this->addToWhereClause("m.name like ?", "%".strtoupper($params->group)."%", "s");
		}
	}
	
	private function addDayTimeWhere(SearchParams $params) {

		if ($params->day) {
			$this->addToWhereClause("m.day = ?", strtoupper($params->day), "s");
		}
		if ($params->startTime || $params->endTime) {
			
			$db = new DB();
			
			$results = array();
			$args = array("");
			
			if ($stmt = $db->prepareStatement("select sequence from times where time = ?", 
					"s", $args, $results, 1)) {

				if ($params->startTime) {
					
					$args[0] = $params->startTime;

					if ($stmt->execute() && $stmt->fetch()) {
						$this->addToWhereClause("t.sequence >= ?", $results[0], "i");
					}
				}
				
				if ($params->endTime) {
					$args[0] = $params->endTime;
					
					if ($stmt->execute() && $stmt->fetch()) {
						$this->addToWhereClause("t.sequence <= ?", $results[0], "i");
					}
				}
			}
			
			$db->closePreparedStatement();
		}
	}
	
	private function addLocationWhere(SearchParams $params) { 

		if ($params->location) {
			
			$this->addToWhereClause("m.location = ?", strtoupper($params->location), "s");
		
		} else {
			if ($params->address) {
				$this->addToWhereClause("m.address1 like ?", strtoupper("%".$params->address."%"), "s");
			}
		
			if ($params->city) {
				$this->addToWhereClause("m.city like ?", strtoupper("%".$params->city."%"), "s");
			}
			if ($params->zip) {
				$this->addToWhereClause("m.zip = ?", $params->zip, "s");
			}
		}
	}
	
	private function addToWhereClause($clause, $value, $type) {
		if ($this->where) { 
			$this->where .= " and ";
		}
		$this->where .= $clause;
		array_push($this->args, $value);
		$this->argTypes .= $type;
	}
	
	public function searchMeetings(SearchParams $params, &$results, &$resultsSort) {

		
		if ($params->searchType == "nearby" && $params->lat && $params->lng) {
			
			$this->buildSqlWithDistance($params);
			$useDistance = true;
			$resultsSort = "location";
			
		} else {
			
			$this->buildSqlNoDistance($params);
			$useDistance = false; 
			$resultsSort = "daytime";
		}
		
		$msg = "";
		$db = new DB();
		$row = array();
		$results = array();

		if (!($stmt = $db->prepareStatement($this->sql, $this->argTypes, 
				$this->args, $row, $this->columnCount))) { 
			$msg = "DB Error: " . $db->getError();
		
		} else {

			if (!$stmt->execute()) {

				$msg = "DB Error: " . $db->getError();

			} else {
							
				while ($stmt->fetch()) {
					$meeting = new MeetingResult();
					$meeting->row = $row[0];
					$meeting->name = $row[1];
					$meeting->location = $row[2];
					$meeting->address1 = $row[3];
					$meeting->address2 = $row[4];
					$meeting->city = $row[5];
					$meeting->zip = $row[6];
					$meeting->locationNotes = $row[7];
					$meeting->quadrant = $row[8];
					$meeting->day = $row[9];
					$meeting->daySequence = $row[10];
					$meeting->time = $row[11];
					$meeting->timeSequence = $row[12];
					$meeting->codes = $row[13];
					$meeting->groupNotes = $row[14];
					$meeting->lat = $row[15];
					$meeting->lng = $row[16];
					
					if ($useDistance) {
						$meeting->distance = $row[17];
					}
	
					array_push($results, $meeting);
				}
			}				
		}
		
		$db->closePreparedStatement();
		return $msg;
	}
}

class SearchParams {
	const NEARBY_SEARCH = "nearby";
	const EXACT_SEARCH = "exact";
	
	public $searchType;
	public $location; 
	public $address;
	public $city;
	public $zip;
	public $radius;
	public $day; 
	public $startTime;
	public $endTime;
	public $group;
	public $codes;
	public $lat; 
	public $lng;
}

class MeetingResult extends Meeting { 
	public $daySequence;
	public $timeSequence;
	public $lat;
	public $lng;
	public $distance;
}

?>